﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace BooksManagermentSystem
{
    public partial class SelectStudentInfo_form : Form
    {
        public SelectStudentInfo_form()
        {
            InitializeComponent();
        }

        private void SelectStudentInfo_form_Load(object sender, EventArgs e)
        {
            comboBox1.Items.Add("学号");
            comboBox1.Items.Add("姓名");
            comboBox1.Items.Add("年级");
            comboBox1.Items.Add("专业");
            comboBox1.SelectedIndex= 0;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string type = comboBox1.SelectedItem.ToString();
            string word = textBox1.Text.Trim();

            string sql = "select a.stuid 学号,a.stuname 姓名,a.grade 年级,a.major 专业,a.arrearage 欠费,b.number 未归还图书数量 " +
                "from StuInfo a left join " +
                "(select stuid ,COUNT(*) as number from RetrieveBook where endtime is null group by stuid)" +
                " as b on a.stuid = b.stuid ";

            if(word.Length> 0 )
            {
                if(type == "学号")
                {
                    sql += $"where a.stuid='{word}'";
                }
                else if(type == "姓名")
                {
                    sql += $"where a.stuname like '%{word}%'";
                }
                else if(type == "年级")
                {
                    sql += $"where a.grade='{word}'";
                }
                else
                {
                    sql += $"where a.major like '%{word}%'";
                }
            }

            dataGridView1.DataSource = DBhelper.GetDataTable(sql);
        }
    }
}
